package com.qyb.controller;


import com.ecmp.util.JsonUtils;
import com.qyb.annotation.ExportName;
import com.qyb.entity.MyExportModel;
import com.qyb.entity.MyImportModel;
import com.qyb.util.ExcelUtils;
import jodd.datetime.JDateTime;
import jodd.typeconverter.Convert;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.springframework.stereotype.Controller;
import org.springframework.util.MultiValueMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * <p>
 * 实现功能：
 * <p/>
 *
 * @author 秦有宝
 * @version 1.0.00
 */
@Controller
@RequestMapping("file")
public class FileController {
    /**
     * 获得IBaseEntityService接口，必须在子类实现
     */
/*    @Override
    protected Object getBaseService() {
        return ApiClient.createProxy(IBorrowRequestService.class);
    }*/

    /**
     * 测试界面
     */
    @RequestMapping(value = "show")
    public String show(HttpServletRequest request) {
        return "main/BatchImportTest";
    }



    /**
     *  导入
     * @param request 请求
     * @param response 响应
     * @throws Exception
     */
    @RequestMapping("BatchImport")
    public void BatchImport(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception {
        String timestamp = request.getParameter("timestamp");
        MultiValueMap<String, MultipartFile> files = request.getMultiFileMap();
        List<String> docList = new ArrayList<String>();
        //清空response
        String dataStr = "";
        if (files != null) {
            for (List<MultipartFile> list : files.values()) {
                MultipartFile file = list.get(0);
                //一次遍历所有文件
                if (file != null && !file.isEmpty()) {
                    //保存上传文件
                    dataStr += ExcelUtils.ReadExcel(file, new String[]{"displayNumber", "startDate", "budgetApplyMoney", "applyMoney", "itemNote"}, MyImportModel.class);
                }
            }
        }

        StringBuilder sb = new StringBuilder();
        sb.append("<script>var data=").append(dataStr).append(";window.parent['").append(timestamp).append("'].getResponse(data);</script>");
        response.setContentType("text/html;charset=UTF-8");
        response.getWriter().write(sb.toString());
        response.getWriter().close();
    }


    /**
     * 下载模板
     *
     * @param request  请求
     * @param response 响应
     */
    @RequestMapping(value = "downloadTemplate")
    public final void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
//        downloadExcelFile(request, response);
        downloadFromPoi(request, response);
    }

    /**
     * 获取文档.
     * 子类重写
     *
     * @param request  请求
     * @param response 响应
     */
    protected void downloadExcelFile(HttpServletRequest request, HttpServletResponse response) {

        //清空response
        response.reset();
        // 设置强制下载不打开
//            response.setContentType("application/force-download");
        response.setContentType("application/octet-stream");
        // 设置文件名
        try {

            String fileName = "template.xls";
                /*
                 * IE，通过URLEncoder对filename进行UTF8编码
                 * 其他的浏览器（firefox、chrome、safari、opera），则要通过字节转换成ISO8859-1
                 */
            if (StringUtils.containsAny(request.getHeader("User-Agent").toUpperCase(), "MSIE", "EDGE")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
        } catch (UnsupportedEncodingException e) {
//                LogUtil.addExceptionLog("文件名编码错误", e);
        }
        byte[] buffer = new byte[2048];
//        InputStream is = this.getClass().getResourceAsStream("/resources/template.xls");
          InputStream is = this.getClass().getResourceAsStream("/template.xls");
//        InputStream is2 = this.getClass().getResourceAsStream("template.xls");
        BufferedInputStream bis = null;
        try {
            bis = new BufferedInputStream(is);
            OutputStream os = response.getOutputStream();
            int i = bis.read(buffer);
            while (i != -1) {
                os.write(buffer, 0, i);
                i = bis.read(buffer);
            }
        } catch (IOException e) {
//                LogUtil.addExceptionLog("文件下载错误", e);
        } finally {
            if (Objects.nonNull(bis)) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取文档.
     * 子类重写
     *
     * @param request  请求
     * @param response 响应
     */
    protected void downloadFromPoi(HttpServletRequest request, HttpServletResponse response){
        //方式1
//        HSSFWorkbook wb = generateTemplate();

        //方式2
        List<MyExportModel> myExportModels = new ArrayList<>();
        MyExportModel myExportModel = new MyExportModel();
        myExportModel.setDisplayNumber("1");
        myExportModel.setApplyMoney(100.2);
        myExportModel.setBudgetApplyMoney(21.0);
        myExportModel.setStartDate(new Date());
        myExportModel.setItemNote("测试导出");
        myExportModel.setFreeze(true);
        myExportModels.add(myExportModel);
        String jsonData = JsonUtils.toJson(myExportModels);
        HSSFWorkbook wb = ExcelUtils.generateExportWorkbook(jsonData, MyExportModel.class, false);


     /*   response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=student.xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();*/
        //清空response
        response.reset();
        // 设置强制下载不打开
//            response.setContentType("application/force-download");
        response.setContentType("application/octet-stream");
        // 设置文件名
        try {

            String fileName = "template.xls";
                /*
                 * IE，通过URLEncoder对filename进行UTF8编码
                 * 其他的浏览器（firefox、chrome、safari、opera），则要通过字节转换成ISO8859-1
                 */
            if (StringUtils.containsAny(request.getHeader("User-Agent").toUpperCase(), "MSIE", "EDGE")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
        } catch (UnsupportedEncodingException e) {
//                LogUtil.addExceptionLog("文件名编码错误", e);
        }
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wb.write(os);
        } catch (IOException e) {
//                LogUtil.addExceptionLog("文件下载错误", e);
        } finally {
            if (Objects.nonNull(os)) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private HSSFWorkbook generateTemplate(){
        List<String> labelNames = Arrays.asList("编号", "起始日期", "已维护预算", "申请金额","备注");
        List<String> datas = Arrays.asList("1", "2015-02-02", "25.25", "25.25","备注说明");
        HSSFWorkbook wb = new HSSFWorkbook();
        String sheetName = WorkbookUtil.createSafeSheetName("表1");
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置列宽: 第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2017-06-01"的宽度为2500
        sheet.setColumnWidth(0, 3766);
        sheet.setColumnWidth(1, 2500);
        sheet.setColumnWidth(2, 3766);
        sheet.setColumnWidth(3, 3766);
        sheet.setColumnWidth(4, 3766);

        //标题样式
        CellStyle labelCellStyle = wb.createCellStyle();
        labelCellStyle.setLocked(true);
        labelCellStyle.setShrinkToFit(false);
        //居中
        labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
        labelCellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        //字体 字号 粗体 颜色
        HSSFFont font  = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);
        font.setBold(true);
        font.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        labelCellStyle.setFont(font);
        //单元格内容自动换行
        labelCellStyle.setWrapText(true);
        //边框
        labelCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        labelCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        labelCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        labelCellStyle.setBorderRight(BorderStyle.THIN);//右边框
        labelCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        labelCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Row labelRow = sheet.createRow((short) 0);
        for (int i = 0; i < labelNames.size(); i++) {
            Cell cell = labelRow.createCell(i);
            cell.setCellValue(labelNames.get(i));
            cell.setCellStyle(labelCellStyle);
        }


        CellStyle dataCellStyle = wb.createCellStyle();
        dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
        dataCellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        HSSFFont font1  = wb.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 9);
//        font.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        dataCellStyle.setFont(font1);
        dataCellStyle.setWrapText(true);
        dataCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        dataCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        dataCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        dataCellStyle.setBorderRight(BorderStyle.THIN);//右边框

        Row dataRow = sheet.createRow((short) (1));
        for (int i = 0; i < datas.size(); i++) {
            Cell cell = dataRow.createCell(i);
            cell.setCellValue(datas.get(i));
            cell.setCellStyle(dataCellStyle);
        }
        return wb;
    }

    /**
     * 导出
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping("BatchExport")
    public void BatchExport(HttpServletRequest request, HttpServletResponse response) throws Exception {
        List<MyExportModel> myExportModels = new ArrayList<>();
        MyExportModel myExportModel = new MyExportModel();
        myExportModel.setDisplayNumber("1");
        myExportModel.setApplyMoney(100.2);
        myExportModel.setBudgetApplyMoney(21.0);
        myExportModel.setStartDate(new Date());
        myExportModel.setItemNote("测试导出");
        myExportModel.setFreeze(true);
        myExportModels.add(myExportModel);

        MyExportModel myExportModel1 = new MyExportModel();
        myExportModel1.setDisplayNumber("2");
        myExportModel1.setApplyMoney(90.2);
        myExportModel1.setBudgetApplyMoney(1.0);
        myExportModel1.setStartDate(new Date());
        myExportModel1.setItemNote("测试导出1");
        myExportModel1.setFreeze(true);
        myExportModels.add(myExportModel1);
        String jsonData = JsonUtils.toJson(myExportModels);
//        System.out.println(jsonData);
        HSSFWorkbook wb = ExcelUtils.generateExportWorkbook(jsonData, MyExportModel.class, true);
     /*   response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=student.xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();*/
        //清空response
        response.reset();
        // 设置强制下载不打开
//            response.setContentType("application/force-download");
        response.setContentType("application/octet-stream");
        // 设置文件名
        try {

            String fileName = "导出数据.xls";
                /*
                 * IE，通过URLEncoder对filename进行UTF8编码
                 * 其他的浏览器（firefox、chrome、safari、opera），则要通过字节转换成ISO8859-1
                 */
            if (StringUtils.containsAny(request.getHeader("User-Agent").toUpperCase(), "MSIE", "EDGE")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
        } catch (UnsupportedEncodingException e) {
//                LogUtil.addExceptionLog("文件名编码错误", e);
        }
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wb.write(os);
        } catch (IOException e) {
//                LogUtil.addExceptionLog("文件下载错误", e);
        } finally {
            if (Objects.nonNull(os)) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
